﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Resources;
using ServicioWebRest.Areas.Api.Controllers;
using System.Data.SqlClient;
using System.Data;

namespace ServicioWebRest.Areas.Api.Models
{
    public class PedidoManager
    {
        ResourceManager rm = new ResourceManager(typeof(RecursosWeb));
        /**************PARAMETROS**********************/
        public List<Dictionary<string, object>> ObtenerParametros(string usu, string tipo)
        {
            // Create the command and the connection.
            string sql = "[sp_extrae_parametros] '" + usu + "'," + tipo + "";
            SqlConnection con = new SqlConnection(rm.GetString("connectionString"));
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable ds = new DataTable();
            // Fill the DataSet.
            da.Fill(ds);
            DataTableaLista(ds);
            return DataTableaLista(ds);
        }
        /***************INSERTAR PEDIDO***************/
        public bool InsertarPedido(Pedido[] ped)
        {
            try
            {
                //Pedidos
                
                SqlConnection con = new SqlConnection(rm.GetString("connectionString"));
                SqlCommand cmd = new SqlCommand("[sp_inserta_pedido]",con);                
                cmd.CommandType = CommandType.StoredProcedure;
                SqlCommand cmd1 = new SqlCommand("[sp_inserta_detalle_pedido]", con);
                cmd1.CommandType = CommandType.StoredProcedure;     
                foreach (Pedido pedi in ped)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@ws_ped_id", SqlDbType.Char, 100);
                    cmd.Parameters.Add("@ws_usu", SqlDbType.Char, 100);
                    cmd.Parameters.Add("@ws_loc_id", SqlDbType.Char, 100);
                    cmd.Parameters.Add("@ws_latitude", SqlDbType.Float);
                    cmd.Parameters.Add("@ws_longitude", SqlDbType.Float);
                    cmd.Parameters.Add("@ws_fecha_inicio", SqlDbType.DateTime);
                    cmd.Parameters.Add("@ws_fecha_fin", SqlDbType.DateTime);
                    cmd.Parameters["@ws_ped_id"].Value = pedi.ped_id;
                    cmd.Parameters["@ws_usu"].Value = pedi.usu_id;
                    cmd.Parameters["@ws_loc_id"].Value = pedi.loc_id;
                    cmd.Parameters["@ws_latitude"].Value = pedi.latitud;
                    cmd.Parameters["@ws_longitude"].Value = pedi.longitud;
                    cmd.Parameters["@ws_fecha_inicio"].Value = pedi.ped_fecha_inicio;
                    cmd.Parameters["@ws_fecha_fin"].Value = pedi.ped_fecha_fin;   
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();

                    // Pedidos Detalle  
                    for (int i=0; i < pedi.deta.Count(); i++)
                    {
                        cmd1.Parameters.Clear();
                        cmd1.Parameters.Add("@ws_ped_id", SqlDbType.Char, 100);
                        cmd1.Parameters.Add("@ws_pro_id", SqlDbType.Char, 100);
                        cmd1.Parameters.Add("@ws_cant", SqlDbType.Int);
                        cmd1.Parameters["@ws_ped_id"].Value = pedi.ped_id;
                        cmd1.Parameters["@ws_pro_id"].Value = pedi.deta[i].pro_id;
                        cmd1.Parameters["@ws_cant"].Value = pedi.deta[i].cant;
                        con.Open();
                        cmd1.ExecuteNonQuery();
                        con.Close();
                    }
                }

                return true;                         
            }
            catch (Exception ex)
            {
                return false;
            }
       }

        /*******************OBTENER PEDIDO********************************/
        public List<Dictionary<string, object>> ObtenerPedido(string usu, string estado, string desde, string hasta)
        {
            // Create the command and the connection.
            string sql = "[sp_extrae_pedidos] '" + usu + "','"+ estado+"','"+desde+"','"+hasta+"'";
            SqlConnection con = new SqlConnection(rm.GetString("connectionString"));
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable ds = new DataTable();
            // Fill the DataSet.
            da.Fill(ds);
            DataTableaLista(ds);
            return DataTableaLista(ds);

        }

        /********************RAZON VISITA******************/
        public bool InsertarRazonVisita(ResultadoVisita[] rev)
        {
            try
            {
                //Pedidos

                SqlConnection con = new SqlConnection(rm.GetString("connectionString"));
                SqlCommand cmd = new SqlCommand("[sp_inserta_resultados_visita]", con);
                cmd.CommandType = CommandType.StoredProcedure;

                foreach (ResultadoVisita resul in rev)
                {
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@ws_loc_id", SqlDbType.Char, 100);
                    cmd.Parameters.Add("@ws_usu", SqlDbType.Char, 100);
                    cmd.Parameters.Add("@ws_fecha_inicio", SqlDbType.DateTime);
                    cmd.Parameters.Add("@ws_fecha_fin", SqlDbType.DateTime);
                    cmd.Parameters.Add("@ws_razon", SqlDbType.Int);
                    cmd.Parameters.Add("@ws_latitud", SqlDbType.Float);
                    cmd.Parameters.Add("@ws_longitud", SqlDbType.Float);
                    cmd.Parameters["@ws_loc_id"].Value = resul.loc_id;
                    cmd.Parameters["@ws_usu"].Value = resul.usuario;
                    cmd.Parameters["@ws_fecha_inicio"].Value = resul.fecha_inicio;
                    cmd.Parameters["@ws_fecha_fin"].Value = resul.fecha_fin;
                    cmd.Parameters["@ws_razon"].Value = resul.razon;
                    cmd.Parameters["@ws_latitud"].Value = resul.latitud;
                    cmd.Parameters["@ws_longitud"].Value = resul.longitud;
                    
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    
                }

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }




        }


        /*****************************TOOL********************/
        public List<Dictionary<string, object>> DataTableaLista(DataTable dt)
        {
            System.Web.Script.Serialization.JavaScriptSerializer serializer = new

            System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows =
              new List<Dictionary<string, object>>();
            Dictionary<string, object> row = null;

            foreach (DataRow dr in dt.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    row.Add(col.ColumnName.Trim(), dr[col]);
                }
                rows.Add(row);
            }
            return rows;
        }
    }
}